/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.aptech.childcare.dao;

import com.aptech.childcare.entities.Activities;
import com.aptech.childcare.until.ConfigDatabase;
import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

/**
 *
 * @author BaoTien
 */
public class ActivitiesDAO {

    private String lastError;
    Connection con = null;
    PreparedStatement pst = null;
    ResultSet rs = null;
    ConfigDatabase db = null;
    final String sqlCREATE = "INSERT INTO tbActivities VALUES(?,?,?)";
    final String sqlREAD = "SELECT * FROM tbActivities";
    final String sqlDELETE = "DELETE FROM tbActivities WHERE ActiveID = ?";
    final String sqlUPDATE = "SELECT * FROM tbActivities WHERE ActiveID = ?";

    public ActivitiesDAO() {
        db = new ConfigDatabase();
    }

    /**
     *
     * @param act the Active to add
     * @return true if add successful, false if add fail
     */
    public boolean create(Activities act) throws IOException {
        if (!exist(act)) {
            return false;
        }
        try {
            con = db.getConnection();
            pst = con.prepareCall(sqlCREATE);
            pst.setString(1, act.getName());
            pst.setFloat(2, act.getFees());
            pst.setString(3, act.getNote());
            if (pst.executeUpdate() > 0) {
                setLastError("Add successfully!");
                return true;
            }
        } catch (SQLException ex) {
            setLastError("SQL error!");
        } finally {
            db.closeConnection(db);
        }
        setLastError("Add fail!");
        return false;
    }

    /**
     *
     * @param act the Active to edit
     * @return true if edit successful, false if edit fail
     */
    public boolean update(Activities act) throws IOException {
        con = db.getConnection();
        try {
            pst = con.prepareStatement(sqlUPDATE, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
            pst.setInt(1, act.getActiveID());
            rs = pst.executeQuery();
            rs.first();
            rs.updateString(2, act.getName());
            rs.updateFloat(3, act.getFees());
            rs.updateString(4, act.getNote());
            rs.updateRow();
            setLastError("Update successfully!");
            return true;
        } catch (SQLException ex) {
            setLastError("SQL Error!");
        } finally {
            db.closeConnection(db);
        }
        //setLastError("Update Fail!");
        return false;
    }

    /**
     *
     * @param act the Active to delete
     * @return true if delete successful, false if delete fail.
     */
    public boolean delete(int activeID) throws IOException {
        con = db.getConnection();
        try {
            pst = con.prepareStatement(sqlDELETE, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
            pst.setInt(1, activeID);
            if (pst.executeUpdate() > 0) {
                setLastError("Delete successfully!");
            }
            return true;
        } catch (SQLException ex) {
            setLastError("SQL Error!");
        } finally {
            db.closeConnection(db);
        }
        return false;
    }

    /**
     * 
     * @param act
     * @return
     */
    public boolean exist(Activities act) throws IOException {
        ArrayList<Activities> list = readByAll();
        for (Activities ac : list) {
            if (act.getName().equals(ac.getName())) {
                setLastError("Activities existed");
                return false;
            }
        }
        return true;
    }

    /**
     *
     * @return list of Activities in database
     */
    public ArrayList<Activities> readByAll() throws IOException {
        ArrayList<Activities> list = new ArrayList<Activities>();
        con = db.getConnection();
        try {
            pst = con.prepareStatement(sqlREAD);
            rs = pst.executeQuery();
            while (rs.next()) {
                Activities act = new Activities();
                act.setActiveID(rs.getInt(1));
                act.setName(rs.getString(2));
                act.setFees(rs.getFloat(3));
                act.setNote(rs.getString(4));
                list.add(act);
            }
        } catch (SQLException ex) {
            setLastError("SQL Error!");
        } finally {
            db.closeConnection(db);
        }
        return list;
    }

    /**
     *
     * @param name the Active needed search
     * @return list of active
     */
    public ArrayList<Activities> readByName(String name) throws IOException {
        ArrayList<Activities> list = readByAll();
        ArrayList<Activities> actList = new ArrayList<Activities>();
        for (Activities act : list) {
            if (act.getName().toLowerCase().matches(".*" + name.trim().toLowerCase() + ".*")) {
                actList.add(act);
            }
        }
        return actList;
    }

    /**
     * @return the lastError
     */
    public String getLastError() {
        return lastError;
    }

    /**
     * @param lastError the lastError to set
     */
    public void setLastError(String lastError) {
        this.lastError = lastError;
    }
}

